Unit
	ActiveRecords;

Interface

Uses
	Classes,
	SysUtils,
	RTTIObject,
	WebComponents,
	DB,
	SQLite3DS;
	
Type
	TActiveRecord = Class;
	TActiveRecordClass = Class Of TActiveRecord;

	TDataSetComponent = Class(TRTTIObject)
	Private
		fFileName,
		fTableName,
		fPrimaryKey : String;
		fDataSet : TSQLite3DataSet;
	Public
		Constructor Create(Const aInstanceName : String); Override;
		Destructor Destroy; Override;
		Procedure Submit; Override;
		Procedure CreateItem(Const Item : TActiveRecord); Virtual;
		Procedure ReloadItem(Const Item : TActiveRecord); Virtual; Overload;
		Procedure UpdateItem(Const Item : TActiveRecord); Virtual;
		Procedure DeleteItem(Const Item : TActiveRecord); Virtual;
		Property DataSet : TSQLite3DataSet Read fDataSet;
	Published
		Property FileName : String Read fFileName Write fFileName;
		Property TableName : String Read fTableName Write fTableName;
		Property PrimaryKey : String Read fPrimaryKey Write fPrimaryKey;
	End;
	
	TActiveRecord = Class(TContainableComponent)
	Private
		fKey : Integer;
		fDataSet : TDataSetComponent;
	Published
		Property Key : Integer Read fKey Write fKey;
		Property DataSet : TDataSetComponent Read fDataSet Write fDataSet;
	End;
	
	TActiveTable = Class(TContainerComponent)
	Private
		fDataSet : TDataSetComponent;
		fSelectQuery,
		fOrderBy : String;
		fRecordCount,
		fBasePage,
		fRecordsPerPage : Integer; // -1 means no limit
		fAutoload,
		fAlreadyLoaded : Boolean;
	Public
		Procedure Process; Override;
		Procedure Load(Var Msg); Message 'load';
		Procedure DoSQLSelect; Virtual;
		Procedure DoDelete(Const aObject : TContainableComponent); Override;
		Procedure SaveChanges(Const aObject : TContainableComponent); Override;
		Procedure AfterAppend(Const aObject : TContainableComponent); Override;
	Published
		Property DataSet : TDataSetComponent Read fDataSet Write fDataSet;
		Property SelectQuery : String Read fSelectQuery Write fSelectQuery;
		Property OrderBy : String Read fOrderBy Write fOrderBy;
		Property RecordCount : Integer Read fRecordCount Write fRecordCount;
		Property BasePage : Integer Read fBasePage Write fBasePage;
		Property RecordsPerPage : Integer Read fRecordsPerPage Write fRecordsPerPage;
		Property Autoload : Boolean Read fAutoload Write fAutoload;
		Property AlreadyLoaded : Boolean Read fAlreadyLoaded Write fAlreadyLoaded;
	End;
	
	TQuerySelector = Class(TEditableComponent)
	Private
		fNumberRecords, 
		fBasePage,
		fRecordsPerPage : Integer;
		fTargetTable : String;
	Public
		Procedure Process; Override;
	Published
		Property NumberRecords : Integer Read fNumberRecords Write fNumberRecords;
		Property BaseRecord : Integer Read fBasePage Write fBasePage;
		Property RecordsPerPage : Integer Read fRecordsPerPage Write fRecordsPerPage;
		Property TargetTable : String Read fTargetTable Write fTargetTable;
	End;

Implementation

Constructor TDataSetComponent.Create(Const aInstanceName : String);
Begin
	Inherited Create(aInstanceName);
	fDataSet := TSQLite3DataSet.Create(Nil);
End;

Destructor TDataSetComponent.Destroy;
Begin
	fDataSet.Free;
End;

Procedure TDataSetComponent.Submit;
Begin
	fDataSet.FileName := fFileName;
	fDataSet.TableName := fTableName;
	fDataSet.PrimaryKey := fPrimaryKey;
End;

Procedure TDataSetComponent.CreateItem(Const Item : TActiveRecord);
Var
	lFields,
	lValues : String;
	
	Procedure GrabFieldNames;
	Var
		lCtrl : Integer;
	Begin
		If Length(Item.Editables) > 0 Then
		Begin
			lFields := Item.Editables[Low(Item.Editables)];
			For lCtrl := Low(Item.Editables) + 1 To High(Item.Editables) Do
				lFields := lFields + ', ' + Item.Editables[lCtrl];
		End
		Else
			lFields := '';
	End;
	
	Procedure GrabValues;
	Var
		lCtrl : Integer;
	Begin
		If Length(Item.Editables) > 0 Then
		Begin
			lValues := '"' + Item.Properties[Item.Editables[Low(Item.Editables)]] + '"';
			For lCtrl := Low(Item.Editables) + 1 To High(Item.Editables) Do
				lValues := lValues + ', "' + Item.Properties[Item.Editables[lCtrl]] + '"';
		End
		Else
			lValues := '';
	End;

Begin
	GrabFieldNames;
	GrabValues;
	// Debug  WriteLn('Creating object ' + Item.InstanceName);
	// Debug  WriteLn('INSERT INTO ' + fDataSet.TableName + '(' + lFields + ') VALUES (' + lValues + ');');
	fDataSet.ExecSQL('INSERT INTO ' + fDataSet.TableName + '(' + lFields + ') VALUES (' + lValues + ');');
End;

Procedure TDataSetComponent.UpdateItem(Const Item : TActiveRecord);
Var
	lValues : String;
	
	Procedure GrabValues;
	Var
		lCtrl : Integer;
	Begin
		If Length(Item.Editables) > 0 Then
		Begin
			lValues := Item.Editables[Low(Item.Editables)] + '="' + Item.Properties[Item.Editables[Low(Item.Editables)]] + '"';
			For lCtrl := Low(Item.Editables) + 1 To High(Item.Editables) Do
				lValues := lValues + ', ' + Item.Editables[lCtrl] +  '="' + Item.Properties[Item.Editables[lCtrl]] + '"';
		End
		Else
			lValues := '';
	End;

Begin
	GrabValues;
	// Debug WriteLn('UPDATE ' + fDataSet.TableName + ' SET ' + lValues + ' WHERE ' + fDataSet.PrimaryKey + '="' + IntToStr(Item.Key) + '";');
	fDataSet.ExecSQL('UPDATE ' + fDataSet.TableName + ' SET ' + lValues + ' WHERE ' + fDataSet.PrimaryKey + '="' + IntToStr(Item.Key) + '";');
End;

Procedure TDataSetComponent.ReloadItem(Const Item : TActiveRecord);
Var
	lCtrl : Integer;
Begin
	For lCtrl := Low(Item.Editables) To High(Item.Editables) Do
		Item.Properties[Item.Editables[lCtrl]] := fDataSet.FieldByName(Item.Editables[lCtrl]).AsString;
	Item.Key := fDataSet.FieldByName(fDataSet.PrimaryKey).AsInteger;
	fDataSet.Next;
End;

Procedure TDataSetComponent.DeleteItem(Const Item : TActiveRecord);
Begin
	fDataSet.ExecSQL('DELETE FROM ' + fDataSet.TableName + ' WHERE ' + fDataSet.PrimaryKey + '="' + IntToStr(Item.Key) + '";');
End;

Procedure TActiveTable.Process;
Begin
	If fAutoload And Not(fAlreadyLoaded) Then
	Begin
		DoSQLSelect;
		fAlreadyLoaded := True;
	End;
End;

Procedure TActiveTable.Load(Var Msg);
Begin
	DoSQLSelect;
End;

Procedure TActiveTable.DoSQLSelect;
Var
	fRealQuery,
	fRealOrder,
	fRealLimit : String;
Begin
	Purge;
	If fSelectQuery <> '' Then
		fRealQuery := ' WHERE ' + fSelectQuery
	Else
		fRealQuery := '';
	If fOrderBy <> '' Then
		fRealOrder := ' ORDER BY ' + fOrderBy
	Else
		fRealOrder := '';
	If fRecordsPerPage > 0 Then
		fRealLimit := ' LIMIT ' + IntToStr(fBasePage) + ',' + IntToStr(fRecordsPerPage)
	Else
		fRealLimit := '';
	fDataSet.DataSet.SQL := 'SELECT COUNT(*) AS Result FROM ' + fDataSet.DataSet.TableName + fRealQuery + ';';
	fDataSet.DataSet.Open;
	fRecordCount := fDataSet.DataSet.FieldByName('Result').AsInteger;
	fDataSet.DataSet.Close;
	// Debug WriteLn('SELECT * FROM ' + fDataSet.DataSet.TableName + fRealQuery + fRealOrder + fRealLimit + ';');
	fDataSet.DataSet.SQL := 'SELECT * FROM ' + fDataSet.DataSet.TableName + fRealQuery + fRealOrder + fRealLimit + ';';
	fDataSet.DataSet.Open;
	// Debug WriteLn(fDataSet.DataSet.RecordCount);
	While Not(fDataSet.DataSet.EOF) Do
	Begin
		// Debug WriteLn('New record object');
		fDataSet.ReloadItem(DoAppend As TActiveRecord);
	End;
End;

Procedure TActiveTable.DoDelete(Const aObject : TContainableComponent);
Begin
	fDataSet.DeleteItem(aObject As TActiveRecord);
	Delete(aObject);
	fAutoload := True;
	fAlreadyLoaded := False;
End;

Procedure TActiveTable.SaveChanges(Const aObject : TContainableComponent);
Begin
	fDataSet.UpdateItem(aObject As TActiveRecord);
End;

Procedure TActiveTable.AfterAppend(Const aObject : TContainableComponent);
Begin
	fDataSet.CreateItem(aObject As TActiveRecord);
	fAlreadyLoaded := False;
End;

Procedure TQuerySelector.Process;
Var
	lTable : TActiveTable;
	lPages : Integer;
	lPageSelector : Integer;
Begin
	lTable := Factory.FindInstance(fTargetTable) As TActiveTable;
	lPages := lTable.RecordCount Div lTable.RecordsPerPage;
	Inherited Process;
End;

Initialization

	Factory.Register(TDataSetComponent);
	Factory.Register(TActiveRecord);
	Factory.Register(TActiveTable);

End.